package cn.ibizlab.modeling.addin.dbschemaimporter.plugin.groovy.sysutil;

import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier
import org.springframework.util.ObjectUtils
import org.springframework.util.StringUtils

import groovy.transform.CompileStatic;
import liquibase.CatalogAndSchema;
import liquibase.database.Database;
import liquibase.database.ObjectQuotingStrategy;
import liquibase.integration.commandline.CommandLineUtils;
import liquibase.resource.FileSystemResourceAccessor;
import liquibase.resource.ResourceAccessor;
import liquibase.snapshot.DatabaseSnapshot;
import liquibase.snapshot.SnapshotControl;
import liquibase.snapshot.SnapshotGeneratorFactory;
import liquibase.structure.core.Column
import liquibase.structure.core.Table;
import net.ibizsys.central.dataentity.IDataEntityRuntime
import net.ibizsys.central.dataentity.action.IDEActionPluginRuntime2;
import net.ibizsys.central.dataentity.service.DEMethodPluginRuntimeRepo;
import net.ibizsys.central.util.IEntityDTO;
import net.ibizsys.model.dataentity.action.IPSDEAction;
import net.ibizsys.modeling.core.dataentity.service.IPSSysDBSchemeService
import net.ibizsys.modeling.core.dataentity.service.IPSSysReqModuleService
import net.ibizsys.runtime.dataentity.IDataEntityRuntimeContext;
import net.ibizsys.runtime.util.DBTypes
import net.ibizsys.runtime.util.DataTypeUtils;
import net.ibizsys.runtime.util.DataTypes;

@CompileStatic
class DBSchemaSysUtilRuntime extends net.ibizsys.central.cloud.core.sysutil.SysUtilRuntimeBase {
	private static final org.apache.commons.logging.Log log = LogFactory.getLog(DBSchemaSysUtilRuntime.class);

	protected final static Map<String, Integer> str2intMap = new HashMap<String, Integer>();
	
	protected final static Map<String, String> dbDriverMap = new HashMap<String, String>();
	
	static {
		dbDriverMap.put(DBTypes.MYSQL5, "com.mysql.jdbc.Driver");
		dbDriverMap.put("MYSQL", "com.mysql.jdbc.Driver");
		dbDriverMap.put(DBTypes.ORACLE, "oracle.jdbc.driver.OracleDriver");
		dbDriverMap.put(DBTypes.POSTGRESQL, "org.postgresql.Driver");
		dbDriverMap.put(DBTypes.PPAS, "com.edb.Driver");
		dbDriverMap.put(DBTypes.SQLITE, "org.sqlite.JDBC");
		dbDriverMap.put(DBTypes.SQLSERVER, "com.microsoft.sqlserver.jdbc.SQLServerDriver");
		dbDriverMap.put(DBTypes.HANA, "com.sap.db.jdbc.Driver");
		dbDriverMap.put(DBTypes.DM, "dm.jdbc.driver.DmDriver");
		dbDriverMap.put(DBTypes.DB2, "com.ibm.db2.jcc.DB2Driver");
		
		str2intMap.put("BIGINT", DataTypes.BIGINT);
		str2intMap.put("BINARY", DataTypes.BINARY);
		str2intMap.put("BIT", DataTypes.BIT);
		str2intMap.put("CHAR", DataTypes.CHAR);
		str2intMap.put("DATETIME", DataTypes.DATETIME);
		str2intMap.put("DECIMAL", DataTypes.DECIMAL);
		str2intMap.put("BIGDECIMAL", DataTypes.BIGDECIMAL);
		str2intMap.put("FLOAT", DataTypes.FLOAT);
		str2intMap.put("IMAGE", DataTypes.IMAGE);
		str2intMap.put("INT", DataTypes.INT);
		str2intMap.put("MONEY", DataTypes.MONEY);
		str2intMap.put("NCHAR", DataTypes.NCHAR);
		str2intMap.put("NTEXT", DataTypes.NTEXT);
		str2intMap.put("NVARCHAR", DataTypes.NVARCHAR);
		str2intMap.put("NUMERIC", DataTypes.NUMERIC);
		str2intMap.put("REAL", DataTypes.REAL);
		str2intMap.put("SMALLDATETIME", DataTypes.SMALLDATETIME);
		str2intMap.put("SMALLINT", DataTypes.SMALLINT);
		str2intMap.put("SMALLMONEY", DataTypes.SMALLMONEY);
		str2intMap.put("SQL_VARIANT", DataTypes.SQL_VARIANT);
		str2intMap.put("SYSNAME", DataTypes.SYSNAME);
		str2intMap.put("TEXT", DataTypes.TEXT);
		str2intMap.put("TIMESTAMP", DataTypes.TIMESTAMP);
		str2intMap.put("TINYINT", DataTypes.TINYINT);
		str2intMap.put("VARBINARY", DataTypes.VARBINARY);
		str2intMap.put("VARCHAR", DataTypes.VARCHAR);
		str2intMap.put("UNIQUEIDENTIFIER", DataTypes.UNIQUEIDENTIFIER);
		str2intMap.put("DATE", DataTypes.DATE);
		str2intMap.put("TIME", DataTypes.TIME);
		//
		str2intMap.put("VARCHAR2", DataTypes.VARCHAR);
		str2intMap.put("NUMBER", DataTypes.DECIMAL);
		str2intMap.put("BLOB", DataTypes.VARBINARY);
		str2intMap.put("CLOB", DataTypes.TEXT);
		str2intMap.put("MEDIUMTEXT", DataTypes.TEXT);
	}
	
	
	private DEMethodPluginRuntimeRepo deMethodPluginRuntimeRepo = new DEMethodPluginRuntimeRepo();
	
	@Autowired
	private IPSSysDBSchemeService iPSSysDBSchemeService = null;
	
	@Autowired
	private IPSSysReqModuleService iPSSysReqModuleService = null;
	
	@Qualifier("UXSYSDBSCHEME")
	private IDataEntityRuntime uxSysDBSchemeDataEntityRuntime = null;
	
	@Qualifier("UXSYSDBTABLE")
	private IDataEntityRuntime uxSysDBTableDataEntityRuntime = null;
	
	@Qualifier("UXSYSDBCOLUMN")
	private IDataEntityRuntime uxSysDBColumnDataEntityRuntime = null;
	
	
	@Override
	protected void onInit() throws Exception {
		this.deMethodPluginRuntimeRepo.init(this.getSystemRuntime(), true);
		super.onInit();
	}
	
	protected DEMethodPluginRuntimeRepo getDEMethodPluginRuntimeRepo() {
		return this.deMethodPluginRuntimeRepo;
	}
	
	
	@Override
	protected void onInstall() throws Exception {
		super.onInstall();
		
		this.onRegisterDEMethodPluginRuntimes();
	}
	
	protected void onRegisterDEMethodPluginRuntimes() throws Exception{
		this.getDEMethodPluginRuntimeRepo().registerDEActionPluginRuntime("UXSYSDBSCHEME", "TESTCONNECTION", new IDEActionPluginRuntime2() {
			@Override
			public Object execute(IDataEntityRuntimeContext iDataEntityRuntimeContext, IPSDEAction iPSDEAction,	Object[] args, Object actionData) throws Throwable {
				return doTestConnection((IEntityDTO)args[0]);
			}
		});
	
		this.getDEMethodPluginRuntimeRepo().registerDEActionPluginRuntime("UXSYSDBSCHEME", "BUILDSCHEME", new IDEActionPluginRuntime2() {
			@Override
			public Object execute(IDataEntityRuntimeContext iDataEntityRuntimeContext, IPSDEAction iPSDEAction,	Object[] args, Object actionData) throws Throwable {
				return doBuildScheme((IEntityDTO)args[0]);
			}
		});
	}
	
	protected Object doTestConnection(IEntityDTO dto) throws Throwable{
		
		String strDBUrl = dto.getString("dburl", null);
		if(!StringUtils.hasLength(strDBUrl)) {
			throw new Exception("未指定数据库jdbc路径");
		}
		
		if(true) {
			String strUserName = DataTypeUtils.getStringValue(dto.get("dbusername"), null);
			String strPassword = DataTypeUtils.getStringValue(dto.get("dbpassword"), null);
			String strDriverClassName = DataTypeUtils.getStringValue(dto.get("driverclassname"), null);
			if(!StringUtils.hasLength(strDriverClassName)) {
				String strDBType = DataTypeUtils.getStringValue(dto.get("dbtype"), null);
				if(!StringUtils.hasLength(strDBType)) {
					throw new Exception("未指定数据库类型");
				}
				
				strDriverClassName = dbDriverMap.get(strDBType.toUpperCase());
				if(!StringUtils.hasLength(strDriverClassName)) {
					throw new Exception("未指定数据库驱动程序");
				}
			}
			
			Database targetDatabase = null;

			//ObjectQuotingStrategy originalStrategy = targetDatabase.getObjectQuotingStrategy();
			
			try {
				// 对比数据库信息
				ResourceAccessor resourceAccessor = new FileSystemResourceAccessor();
	
				targetDatabase = CommandLineUtils.createDatabaseObject(resourceAccessor, strDBUrl, strUserName, strPassword, strDriverClassName, "", "", false, false, null, null, null, null, null, null, null);
	
				String strTypes = "Column,Table,PrimaryKey"; // Column,Table,Index,ForeignKey,Index,PrimaryKey
				SnapshotControl snapshotControl = new SnapshotControl(targetDatabase, strTypes);
				
				targetDatabase.setObjectQuotingStrategy(ObjectQuotingStrategy.QUOTE_ALL_OBJECTS);
				DatabaseSnapshot databaseSnapshot = SnapshotGeneratorFactory.getInstance().createSnapshot(CatalogAndSchema.DEFAULT, targetDatabase, snapshotControl);
				dto.set("testresult", "ok");
			}
			catch (Throwable ex) {
				dto.set("testresult", ex.getMessage());
			}
			finally {
				if(targetDatabase!=null) {
					try {
						targetDatabase.close();
					}
					catch (Throwable ex) {
						log.error(ex);
					}
				}
			}
		}
				
		return dto;
	}
	
	protected Object doBuildScheme(IEntityDTO dto) throws Throwable{
		
		String strDBUrl = dto.getString("dburl", null);
		if(!StringUtils.hasLength(strDBUrl)) {
			throw new Exception("未指定数据库jdbc路径");
		}
		
		if(true) {
			String strUserName = DataTypeUtils.getStringValue(dto.get("dbusername"), null);
			String strPassword = DataTypeUtils.getStringValue(dto.get("dbpassword"), null);
			String strDriverClassName = DataTypeUtils.getStringValue(dto.get("driverclassname"), null);
			if(!StringUtils.hasLength(strDriverClassName)) {
				String strDBType = DataTypeUtils.getStringValue(dto.get("dbtype"), null);
				if(!StringUtils.hasLength(strDBType)) {
					throw new Exception("未指定数据库类型");
				}
				
				strDriverClassName = dbDriverMap.get(strDBType.toUpperCase());
				if(!StringUtils.hasLength(strDriverClassName)) {
					throw new Exception("未指定数据库驱动程序");
				}
			}
			
			Database targetDatabase = null;

			//ObjectQuotingStrategy originalStrategy = targetDatabase.getObjectQuotingStrategy();
			
			try {
				// 对比数据库信息
				ResourceAccessor resourceAccessor = new FileSystemResourceAccessor();
	
				targetDatabase = CommandLineUtils.createDatabaseObject(resourceAccessor, strDBUrl, strUserName, strPassword, strDriverClassName, "", "", false, false, null, null, null, null, null, null, null);
	
				String strTypes = "Column,Table,PrimaryKey"; // Column,Table,Index,ForeignKey,Index,PrimaryKey
				SnapshotControl snapshotControl = new SnapshotControl(targetDatabase, strTypes);
				
				targetDatabase.setObjectQuotingStrategy(ObjectQuotingStrategy.QUOTE_ALL_OBJECTS);
				DatabaseSnapshot databaseSnapshot = SnapshotGeneratorFactory.getInstance().createSnapshot(CatalogAndSchema.DEFAULT, targetDatabase, snapshotControl);
				Set<Table> tables = databaseSnapshot.get(Table.class);
				List<IEntityDTO> psSysDBTableDTOList = new ArrayList<IEntityDTO>();
				if(!ObjectUtils.isEmpty(tables)) {
					
					if(!ObjectUtils.isEmpty(tables)) {
						for(Table table : tables) {
							IEntityDTO psSysDBTableDTO = this.buildUXSysDBTableDTO(table);
							psSysDBTableDTOList.add(psSysDBTableDTO);
						}	
					}
				}
				dto.set("pssysdbtables", psSysDBTableDTOList);
				
			}
			catch (Throwable ex) {
				throw new Exception(String.format("构建数据库模型体系发生异常，%1\$s", ex.getMessage()), ex);
			}
			finally {
				if(targetDatabase!=null) {
					try {
						targetDatabase.close();
					}
					catch (Throwable ex) {
						log.error(ex);
					}
				}
			}
		}
				
		return dto;
	}
	
	protected IEntityDTO buildUXSysDBTableDTO(Table table) {
		IEntityDTO psSysDBTableDTO = this.uxSysDBTableDataEntityRuntime.createEntity();
//		psSysDBTableDTO.setPSSysDBSchemeId(this.getPSSysDBSchemeDTO().getPSSysDBSchemeId());
//		psSysDBTableDTO.setPSSysDBSchemeName(this.getPSSysDBSchemeDTO().getPSSysDBSchemeName());
		psSysDBTableDTO.set("tabletype", "TABLE");
		psSysDBTableDTO.set("pssysdbtablename", table.getName().toUpperCase());
		psSysDBTableDTO.set("codename", table.getName());
		
		List<IEntityDTO> psSysDBColumnDTOList = new ArrayList<IEntityDTO>();
		List<Column> columns = table.getColumns();
		if(!ObjectUtils.isEmpty(columns)) {
			Map<String, Column> pkeyMap = new HashMap<String, Column>();
			
			if(table.getPrimaryKey()!=null && !ObjectUtils.isEmpty(table.getPrimaryKey().getColumns())) {
				for(Column column : table.getPrimaryKey().getColumns()) {
					pkeyMap.put(column.getName().toUpperCase(), column);
				}
			}
			for(Column column : columns) {
				IEntityDTO psSysDBColumnDTO = this.uxSysDBColumnDataEntityRuntime.createEntity();
				
				//psSysDBColumnDTO.set("PSSysDBTableId", psSysDBTableDTO.getPSSysDBTableId());
				psSysDBColumnDTO.set("PSSysDBTableName", table.getName().toUpperCase());
				psSysDBColumnDTO.set("PSSysDBColumnName", column.getName().toUpperCase());
				psSysDBColumnDTO.set("CodeName", column.getName());
				psSysDBColumnDTO.set("OrderValue", column.getOrder());
				
				psSysDBColumnDTO.set("DataType", column.getType().toString());
				psSysDBColumnDTO.set("AllowEmpty", 1);
				
				//判断是否主键
				if(pkeyMap.containsKey(column.getName().toUpperCase())){
					psSysDBColumnDTO.set("PKey", 1);
					psSysDBColumnDTO.set("AllowEmpty", 0);
				}
				else {
					psSysDBColumnDTO.set("AllowEmpty", column.isNullable()?1:0);
				}
				
				//填充标准数据类型
				String strType = column.getType().toString();
				Integer nLength = null;
				Integer nPrecision = null;
				int nPos = strType.indexOf("(");
				if(nPos != -1) {
					strType = strType.replace(")","");
					strType = strType.replace("(","|");
					String[] items = strType.split("[|]");
					strType = items[0];
					String strLength = items[1];
					if(StringUtils.hasLength(strLength)) {
						items = strLength.split("[,]");
						strLength = items[0];
						String[] items2 = strLength.trim().split("[ ]");
						nLength = Integer.parseInt(items2[0]);
						if(items.length ==2) {
							nPrecision = Integer.parseInt(items[1].trim());
						}
					}
					
					if(nLength != null && nLength <0) {
						nLength = null;
					}
					
					if(nLength!=null) {
						if(nPrecision != null && nPrecision <0) {
							nPrecision = null;
						}
					}
					else {
						nPrecision = null;
					}
				}
				
				psSysDBColumnDTO.set("DataType", strType.toUpperCase());
				psSysDBColumnDTO.set("Length", nLength);
				psSysDBColumnDTO.set("Precision2", nPrecision);
				
				if(str2intMap.containsKey(strType.toUpperCase())){
					int nStdDataType = str2intMap.get(strType.toUpperCase());
					psSysDBColumnDTO.set("StdDataType", nStdDataType);
				}
				else {
					log.warn(String.format("无法识别的数据类型[%1\$s]", column.getType().toString()));
					continue;
				}
				
				psSysDBColumnDTOList.add(psSysDBColumnDTO);
			}
		}
		
		psSysDBTableDTO.set("pssysdbcolumns", psSysDBColumnDTOList);
		
		return psSysDBTableDTO;
	}

	
	@Override
	protected void onUninstall() throws Throwable {
		this.getDEMethodPluginRuntimeRepo().shutdown();
		super.onUninstall();
	}
	
	
}
